package net.ib.mtalk.database;

import net.ib.mtalk.database.DatabaseContract.BuddyColumns;
import net.ib.mtalk.database.DatabaseContract.BuddyTable;
import net.ib.mtalk.database.DatabaseContract.ContactColumns;
import net.ib.mtalk.database.DatabaseContract.ContactTable;
import net.ib.mtalk.database.DatabaseContract.GroupColumns;
import net.ib.mtalk.database.DatabaseContract.GroupTable;
import net.ib.mtalk.database.DatabaseContract.MessageColumns;
import net.ib.mtalk.database.DatabaseContract.MessageTable;
import net.ib.mtalk.database.DatabaseContract.ParticipantsColumns;
import net.ib.mtalk.database.DatabaseContract.ParticipantsTable;
import net.ib.mtalk.database.DatabaseContract.ProfileColumns;
import net.ib.mtalk.database.DatabaseContract.SuggestionColumns;
import net.ib.mtalk.database.DatabaseContract.SuggestionTable;
import net.ib.mtalk.database.DatabaseContract.ViewColumns;
import net.ib.mtalk.database.DatabaseContract.ViewMemberColumns;
import net.ib.mtalk.database.DatabaseContract.ViewTable;
import android.content.ContentResolver;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

/**
 * 
 * 
 * @author happy2v
 *
 */
public class DatabaseHelper extends SQLiteOpenHelper{

	private static final String DATABASE_NAME = "mtalk.db";
	private static final int DATABASE_VERSION = 1;

	private static DatabaseHelper instance = null;

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public static DatabaseHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DatabaseHelper(context);;
		}
		return instance;
	}
	
	@Override
	public void onCreate(SQLiteDatabase db) {		
		db.execSQL(Tables.CREATE_TABLE_BUDDY);
		db.execSQL(Tables.CREATE_TABLE_GROUP);
		db.execSQL("INSERT INTO " + Tables.TABLE_NAME_GROUP + " VALUES (null, 'new Buddy', '0')");
		db.execSQL("INSERT INTO " + Tables.TABLE_NAME_GROUP + " VALUES (null, 'Not Assigned', '0')");
		db.execSQL(Tables.CREATE_TABLE_CONTACT);
		db.execSQL(Tables.CREATE_TABLE_PARTICIPANTS);
		db.execSQL(Tables.CREATE_TABLE_MESSAGE);
		db.execSQL(Tables.CREATE_INDEX_MESSAGE);
		db.execSQL(Tables.CREATE_TABLE_VIEW);
		db.execSQL(Tables.CREATE_TABLE_VIEW_MEMBERS);
		db.execSQL(Tables.CREATE_TABLE_SUGGESTION);
		db.execSQL(Tables.TRIGGER_MESSAGES_DELETE);
		db.execSQL(Tables.CREATE_TABLE_PROFILE);
	}
	
	public interface Tables {
		/** Buddy Table 의 이름 */
		public static final String TABLE_NAME_BUDDY			= "buddy";
		public static final String TABLE_NAME_GROUP			= "phone_group";
		public static final String TABLE_NAME_CONTACT		= "contact";
		public static final String TABLE_NAME_PARTICIPANTS	= "participants";
		public static final String TABLE_NAME_MESSAGE		= "msg";
		public static final String INDEX_NAME_MESSAGE		= "index_msg";
		public static final String INDEX_NAME_MESSAGE_VIEW	= "index_msg_view";
		public static final String TABLE_NAME_VIEW			= "view";
		public static final String TABLE_NAME_VIEW_MEMBERS	= "view_members";
		public static final String TABLE_NAME_SUGGESTION	= "suggestion";
		public static final String TABLE_NAME_PROFILE		= "profile";
				
		String CREATE_TABLE_BUDDY = 
			"CREATE TABLE " + Tables.TABLE_NAME_BUDDY + " ("
				+ BaseColumns._ID							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ BuddyColumns.KEY_BUDDY_UUID_MSB			+ " NUMBER NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_UUID_LSB			+ " NUMBER NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_STATUS_TYPE		+ " NUMBER NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_FAVORITE			+ " NUMBER DEFAULT 0,"
				+ BuddyColumns.KEY_BUDDY_INFO				+ " BLOB,"
				+ BuddyColumns.KEY_BUDDY_DISPLAYNAME		+ " TEXT NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_DISPLAYNICK		+ " TEXT NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_NEW_FRIEND			+ " NUMBER NOT NULL DEFAULT 1,"
				+ BuddyColumns.KEY_BUDDY_CHOSUNG			+ " TEXT NOT NULL,"
				+ BuddyColumns.KEY_BUDDY_IMG_VERSION		+ " NUMBER NOT NULL DEFAULT 0,"
				+ "UNIQUE (" + BuddyColumns.KEY_BUDDY_UUID_MSB + ","
					+ BuddyColumns.KEY_BUDDY_UUID_LSB + ") ON CONFLICT IGNORE)";

		String CREATE_TABLE_GROUP = 
			"CREATE TABLE " + Tables.TABLE_NAME_GROUP + " ("
				+ BaseColumns._ID							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ GroupColumns.KEY_GROUP_NAME				+ " TEXT NOT NULL,"
				+ GroupColumns.KEY_GROUP_SET_FOLD			+ " NUMBER DEFAULT 0,"
				+ "UNIQUE (" + GroupColumns.KEY_GROUP_NAME + ") ON CONFLICT IGNORE)"; 

		String CREATE_TABLE_CONTACT = 
			"CREATE TABLE " + Tables.TABLE_NAME_CONTACT + " ("
				+ BaseColumns._ID							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ ContactColumns.KEY_CONTACT_PHONE_ID		+ " NUMBER NOT NULL,"
				+ ContactColumns.KEY_CONTACT_NUMBER			+ " TEXT NOT NULL,"
				+ ContactColumns.KEY_CONTACT_IDENTIFIER		+ " TEXT,"
				+ ContactColumns.KEY_CONTACT_TYPE			+ " NUMBER,"				
				+ ContactColumns.KEY_CONTACT_VERSION		+ " NUMBER,"				
				+ ContactColumns.KEY_CONTACT_NAME			+ " TEXT,"				
				+ ContactColumns.KEY_BUDDY_UUID_MSB			+ " NUMBER,"				
				+ ContactColumns.KEY_BUDDY_UUID_LSB			+ " NUMBER,"				
				+ "UNIQUE (" + ContactColumns.KEY_CONTACT_PHONE_ID + "," + ContactColumns.KEY_CONTACT_NUMBER + ") ON CONFLICT IGNORE)";
		
		String CREATE_TABLE_PARTICIPANTS = 
			"CREATE TABLE " + Tables.TABLE_NAME_PARTICIPANTS + " ("
				+ BaseColumns._ID								+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ ParticipantsColumns.KEY_PARTICIPANTS_GROUP	+ " NUMBER NOT NULL,"
				+ ParticipantsColumns.KEY_PARTICIPANTS_BUDDY	+ " NUMBER NOT NULL,"
				+ "UNIQUE (" + ParticipantsColumns.KEY_PARTICIPANTS_GROUP + "," + ParticipantsColumns.KEY_PARTICIPANTS_BUDDY + ") ON CONFLICT REPLACE)";

		String CREATE_TABLE_MESSAGE = 
			"CREATE TABLE " + Tables.TABLE_NAME_MESSAGE + " ("
				+ BaseColumns._ID 								+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ MessageColumns.KEY_CLIENT_VIEW_UUID_MSB		+ " NUMBER NOT NULL,"
				+ MessageColumns.KEY_CLIENT_VIEW_UUID_LSB		+ " NUMBER NOT NULL,"
				+ MessageColumns.KEY_VIEW_UUID_MSB				+ " NUMBER,"
				+ MessageColumns.KEY_VIEW_UUID_LSB				+ " NUMBER,"
				+ MessageColumns.KEY_CLIENT_MESSAGE_UUID_MSB	+ " NUMBER NOT NULL,"
				+ MessageColumns.KEY_CLIENT_MESSAGE_UUID_LSB	+ " NUMBER NOT NULL,"
				+ MessageColumns.KEY_MESSAGE_UUID_MSB			+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_UUID_LSB			+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_NOTI_UUID_MSB		+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_NOTI_UUID_LSB		+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_TIME				+ " NUMBER NOT NULL,"
				+ MessageColumns.KEY_MESSAGE_STATUS				+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_OBJECT_TYPE		+ " NUMBER,"
				+ MessageColumns.KEY_SEND_MESSAGE				+ " NUMBER,"
				+ MessageColumns.KEY_MESSAGE_PROPERTY			+ " NUMBER DEFAULT 0,"
				+ MessageColumns.KEY_FILE_URI					+ " TEXT,"
				+ MessageColumns.KEY_FILE_TRANSFER_INFO			+ " BLOB,"
				+ MessageColumns.KEY_MESSAGE					+ " BLOB,"
				+ MessageColumns.KEY_THUMBNAIL					+ " BLOB,"
				+ MessageColumns.KEY_MESSAGE_UNREAD_COUNT		+ " NUMBER);";		
		
		final String CREATE_INDEX_MESSAGE = "create index " + Tables.INDEX_NAME_MESSAGE
		+ " on " + 				Tables.TABLE_NAME_MESSAGE
		+ " ("	 + 				MessageColumns.KEY_MESSAGE_UUID_MSB
		+ ", "	 + 				MessageColumns.KEY_MESSAGE_UUID_LSB
		+ ");";
		
		final String CREATE_INDEX_MESSAGE_VIEW = "create index " + Tables.INDEX_NAME_MESSAGE_VIEW
		+ " on " + 				Tables.TABLE_NAME_MESSAGE
		+ " ("	 + 				MessageColumns.KEY_VIEW_UUID_MSB
		+ ", "	 + 				MessageColumns.KEY_VIEW_UUID_LSB
		+ ");";
		
		String CREATE_TABLE_VIEW = 
			"CREATE TABLE " + Tables.TABLE_NAME_VIEW + " ("
				+ BaseColumns._ID 							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ ViewColumns.KEY_CLIENT_VIEW_UUID_MSB		+ " NUMBER NOT NULL,"
				+ ViewColumns.KEY_CLIENT_VIEW_UUID_LSB		+ " NUMBER NOT NULL,"
				+ ViewColumns.KEY_VIEW_UUID_MSB				+ " NUMBER,"
				+ ViewColumns.KEY_VIEW_UUID_LSB				+ " NUMBER,"
				+ ViewColumns.KEY_RECEIVER_UUID_MSB			+ " NUMBER," // 1:1 대화가 존재하는지 파악을 하기 위한 필드
				+ ViewColumns.KEY_RECEIVER_UUID_LSB			+ " NUMBER,"
				+ ViewColumns.KEY_CHAT_TYPE					+ " NUMBER,"
				+ ViewColumns.KEY_TITLE						+ " TEXT,"
				+ ViewColumns.KEY_SUB_TITLE					+ " TEXT,"
				+ ViewColumns.KEY_MESSAGE_LAST_TIME			+ " NUMBER NOT NULL,"
				+ ViewColumns.KEY_VIEW_UNREAD_COUNT			+ " NUMBER,"
				+ ViewColumns.KEY_VIEW_MEMBER_COUNT			+ " NUMBER,"
				+ ViewColumns.KEY_VIEW_PUSH_ONOFF			+ " NUMBER,"
				+ ViewColumns.KEY_VIEW_ENABLE				+ " NUMBER,"
				+ ViewColumns.KEY_RECEIVER_LIST				+ " BLOB,"
				+ "UNIQUE (" + ViewColumns.KEY_VIEW_UUID_MSB + "," + ViewColumns.KEY_VIEW_UUID_LSB + ") ON CONFLICT REPLACE)";
		
		StringBuffer sb = new StringBuffer()
			.append("CREATE TABLE %s ( ")
			.append(" %s INTEGER PRIMARY KEY AUTOINCREMENT, ")
			.append(" %s NUMBER NOT NULL, ") /** VIEW_MSB */
			.append(" %s NUMBER NOT NULL, ") /** VIEW_LSB */
			.append(" %s NUMBER NOT NULL, ") /** BUDDY_MSB */
			.append(" %s NUMBER NOT NULL, ") /** BUDDY_LSB */
			.append(" %s TEXT, ") /** BUDDY_NAME */
			.append(" UNIQUE ( %s, %s, %s, %s) ON CONFLICT REPLACE)");
		String CREATE_TABLE_VIEW_MEMBERS = String.format(sb.toString(), 
				// TABLE NAME
				Tables.TABLE_NAME_VIEW_MEMBERS,
				// 6 columns
				BaseColumns._ID,
				ViewMemberColumns.KEY_VIEW_UUID_MSB, ViewMemberColumns.KEY_VIEW_UUID_LSB,
				ViewMemberColumns.KEY_BUDDY_UUID_MSB, ViewMemberColumns.KEY_BUDDY_UUID_LSB, 
				ViewMemberColumns.KEY_BUDDY_NAME,
				// 4 UINQUE
				ViewMemberColumns.KEY_VIEW_UUID_MSB, ViewMemberColumns.KEY_VIEW_UUID_LSB,
				ViewMemberColumns.KEY_BUDDY_UUID_MSB, ViewMemberColumns.KEY_BUDDY_UUID_LSB);
		
		String CREATE_TABLE_SUGGESTION = 
			"CREATE TABLE " + Tables.TABLE_NAME_SUGGESTION + " ("
				+ BaseColumns._ID 							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ SuggestionColumns.KEY_SUGGESTION_UUID_MSB	+ " NUMBER NOT NULL,"
				+ SuggestionColumns.KEY_SUGGESTION_UUID_LSB	+ " NUMBER NOT NULL,"
				+ SuggestionColumns.KEY_SCORE				+ " NUMBER NOT NULL,"
				+ SuggestionColumns.KEY_KNOWS_OPTIONS		+ " BLOB,"		
				+ SuggestionColumns.KEY_MUTUAL_OPTIONS		+ " BLOB,"		
				+ SuggestionColumns.KEY_STATUS_TYPE			+ " NUMBER NOU NULL,"		
				+ SuggestionColumns.KEY_DISPLAYNAME			+ " TEXT NOU NULL,"		
				+ "UNIQUE (" + SuggestionColumns.KEY_SUGGESTION_UUID_MSB + "," + SuggestionColumns.KEY_SUGGESTION_UUID_LSB + ") ON CONFLICT REPLACE)";
		
		/** ViewTable이 삭제되면 그 View 에 대한 Message 를 모두 삭제한다. */
		String TRIGGER_MESSAGES_DELETE = "create trigger " +  ViewTable.TRIGGER_MESSAGES_DELETE
				+ " AFTER DELETE ON "
				+ Tables.TABLE_NAME_VIEW
				+ " FOR EACH ROW"
				+ " BEGIN"
				+ " DELETE FROM " 
				+ Tables.TABLE_NAME_MESSAGE
				+ " WHERE "
				+ MessageTable.KEY_CLIENT_VIEW_UUID_MSB
				+ " = old."
				+ ViewTable.KEY_CLIENT_VIEW_UUID_MSB
				+ " AND "
				+ MessageTable.KEY_CLIENT_VIEW_UUID_LSB
				+ " = old."
				+ ViewTable.KEY_CLIENT_VIEW_UUID_LSB
				+ ";"
				+ " END";

		String CREATE_TABLE_PROFILE = 
			"CREATE TABLE " + Tables.TABLE_NAME_PROFILE + " ("
			+ BaseColumns._ID 							+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ ProfileColumns.KEY_PROFILE_UUID_MSB		+ " NUMBER NOT NULL,"
			+ ProfileColumns.KEY_PROFILE_UUID_LSB		+ " NUMBER NOT NULL,"
			+ ProfileColumns.KEY_IS_UPLOAD				+ " NUMBER,"
			+ ProfileColumns.KEY_FILE_TRANSFER_INFO		+ " BLOB,"
			+ ProfileColumns.KEY_FILE_OPTIONS			+ " BLOB,"
			+ "UNIQUE (" + ProfileColumns.KEY_PROFILE_UUID_MSB + "," + ProfileColumns.KEY_PROFILE_UUID_LSB + "," + ProfileColumns.KEY_IS_UPLOAD	+ ") ON CONFLICT IGNORE)";
	}
	
	public interface Buddy_Query{
		String BUDDY_GROUP
		=	"("
		+	"SELECT "  + "b." + ParticipantsTable._ID+","
					   + ParticipantsTable.KEY_PARTICIPANTS_BUDDY+","
					   + ParticipantsTable.KEY_PARTICIPANTS_GROUP+","
					   + BuddyTable.KEY_BUDDY_STATUS_TYPE+","
					   + BuddyTable.KEY_BUDDY_UUID_MSB+","
					   + BuddyTable.KEY_BUDDY_UUID_LSB+","
					   + BuddyTable.KEY_BUDDY_FAVORITE+","
					   + BuddyTable.KEY_BUDDY_DISPLAYNAME+","
					   + BuddyTable.KEY_BUDDY_DISPLAYNICK+","
					   + BuddyTable.KEY_BUDDY_INFO+","
					   + BuddyTable.KEY_BUDDY_NEW_FRIEND+","
					   + BuddyTable.KEY_BUDDY_CHOSUNG+","
					   + BuddyTable.KEY_BUDDY_IMG_VERSION
		+	" FROM "   + Tables.TABLE_NAME_BUDDY + " a"
					   + " JOIN " + Tables.TABLE_NAME_PARTICIPANTS + " b"
					    			+ " ON " + "a." + BuddyTable._ID + " = " + "b." + ParticipantsTable.KEY_PARTICIPANTS_BUDDY
		+	")";

		String BUDDY_INFO
		=	"("
			+	"SELECT "  + ParticipantsTable.KEY_PARTICIPANTS_GROUP+","
						   + ParticipantsTable.KEY_PARTICIPANTS_BUDDY+","
					       + GroupTable.KEY_GROUP_NAME
			+	" FROM "   + Tables.TABLE_NAME_GROUP + " a"
						   + " JOIN " + Tables.TABLE_NAME_PARTICIPANTS + " b"
						   + " ON " + "a." + GroupTable._ID + " = " + "b." + ParticipantsTable.KEY_PARTICIPANTS_GROUP
			+	")";
		
		String BUDDY_NOT_GROUP
		= "("
		+ "SELECT " + BuddyTable._ID +","
					+ BuddyTable.KEY_BUDDY_STATUS_TYPE+","
					+ BuddyTable.KEY_BUDDY_UUID_MSB+","
					+ BuddyTable.KEY_BUDDY_UUID_LSB+","
					+ BuddyTable.KEY_BUDDY_FAVORITE+","
					+ BuddyTable.KEY_BUDDY_DISPLAYNAME+","
					+ BuddyTable.KEY_BUDDY_DISPLAYNICK+","
					+ BuddyTable.KEY_BUDDY_INFO+","
					+ BuddyTable.KEY_BUDDY_NEW_FRIEND+","
					+ BuddyTable.KEY_BUDDY_CHOSUNG+","
					+ BuddyTable.KEY_BUDDY_IMG_VERSION
		+ " FROM "  + Tables.TABLE_NAME_BUDDY
//					+ " LEFT OUTER JOIN " + Tables.TABLE_NAME_BUDDY + " b"
//								 + " ON " + "a." + ParticipantsTable.KEY_PARTICIPANTS_BUDDY + " != " + "b." + BuddyTable._ID
		+ " WHERE " + BuddyTable._ID + " NOT IN (SELECT DISTINCT " 
					+ ParticipantsTable.KEY_PARTICIPANTS_BUDDY + " FROM "
					+ Tables.TABLE_NAME_PARTICIPANTS + ")"
		+ ")";
		
		String BUDDY_UNION
		= "("
			+ "SELECT " + BuddyTable._ID +","
						+ BuddyTable.KEY_BUDDY_STATUS_TYPE+","
						+ BuddyTable.KEY_BUDDY_UUID_MSB+","
						+ BuddyTable.KEY_BUDDY_UUID_LSB+","
						+ BuddyTable.KEY_BUDDY_FAVORITE+","
						+ BuddyTable.KEY_BUDDY_DISPLAYNAME+","
						+ BuddyTable.KEY_BUDDY_DISPLAYNICK+","
						+ BuddyTable.KEY_BUDDY_INFO+","
						+ BuddyTable.KEY_BUDDY_NEW_FRIEND +","
						+ BuddyTable.KEY_BUDDY_CHOSUNG+","
						+ BuddyTable.KEY_BUDDY_IMG_VERSION
						+ " FROM ("
				+ "SELECT " + BuddyTable._ID +","
							+ BuddyTable.KEY_BUDDY_STATUS_TYPE+","
							+ BuddyTable.KEY_BUDDY_UUID_MSB+","
							+ BuddyTable.KEY_BUDDY_UUID_LSB+","
							+ BuddyTable.KEY_BUDDY_FAVORITE+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNAME+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNICK+","
							+ BuddyTable.KEY_BUDDY_INFO+","
							+ BuddyTable.KEY_BUDDY_NEW_FRIEND +","
							+ BuddyTable.KEY_BUDDY_CHOSUNG+","
							+ BuddyTable.KEY_BUDDY_IMG_VERSION
				+ " FROM "  + Tables.TABLE_NAME_BUDDY
				+ " WHERE " + BuddyTable.KEY_BUDDY_NEW_FRIEND + "=1 AND "
							+ BuddyTable.KEY_BUDDY_STATUS_TYPE + "=1"
				+ " ORDER BY " 
					+ "case" 
					+ " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'ㄱ' AND '힣'  then 1 "
					+ " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'A' AND 'Z' then 2 "  
				    + " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'a' AND 'z' then 3 "           
				    + " else 4 end, "+BuddyTable.KEY_BUDDY_DISPLAYNICK + " COLLATE LOCALIZED ASC"
				+ ")"
			+ " UNION ALL "
				+ "SELECT " + BuddyTable._ID +","
							+ BuddyTable.KEY_BUDDY_STATUS_TYPE+","
							+ BuddyTable.KEY_BUDDY_UUID_MSB+","
							+ BuddyTable.KEY_BUDDY_UUID_LSB+","
							+ BuddyTable.KEY_BUDDY_FAVORITE+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNAME+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNICK+","
							+ BuddyTable.KEY_BUDDY_INFO+","
							+ BuddyTable.KEY_BUDDY_NEW_FRIEND +","
							+ BuddyTable.KEY_BUDDY_CHOSUNG+","
							+ BuddyTable.KEY_BUDDY_IMG_VERSION
							+ " FROM ("
				+ "SELECT " + BuddyTable._ID +","
							+ BuddyTable.KEY_BUDDY_STATUS_TYPE+","
							+ BuddyTable.KEY_BUDDY_UUID_MSB+","
							+ BuddyTable.KEY_BUDDY_UUID_LSB+","
							+ BuddyTable.KEY_BUDDY_FAVORITE+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNAME+","
							+ BuddyTable.KEY_BUDDY_DISPLAYNICK+","
							+ BuddyTable.KEY_BUDDY_INFO+","
							+ BuddyTable.KEY_BUDDY_NEW_FRIEND +","
							+ BuddyTable.KEY_BUDDY_CHOSUNG+","
							+ BuddyTable.KEY_BUDDY_IMG_VERSION
				+ " FROM "  + Tables.TABLE_NAME_BUDDY
				+ " WHERE " + BuddyTable.KEY_BUDDY_STATUS_TYPE + "='1'"
				+ " ORDER BY " 
					+ "case" 
					+ " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'ㄱ' AND '힣'  then 1 "
					+ " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'A' AND 'Z' then 2 "  
				    + " when substr("+BuddyTable.KEY_BUDDY_DISPLAYNICK+", 1, 1) BETWEEN 'a' AND 'z' then 3 "           
				    + " else 4 end, "+BuddyTable.KEY_BUDDY_DISPLAYNICK + " COLLATE LOCALIZED ASC"
				+ ")"
			+ ")";
	}
	public interface Contact_Query{
		String CONTACT_BUDDY
		= "("
		+ "SELECT " + "b." + BuddyTable._ID+","
					+ "a." + ContactTable.KEY_CONTACT_PHONE_ID+","
					+ "a." + ContactTable.KEY_CONTACT_NUMBER+","
					+ "b." + BuddyTable.KEY_BUDDY_UUID_MSB+","
					+ "b." + BuddyTable.KEY_BUDDY_UUID_LSB+","
					+ "b." + BuddyTable.KEY_BUDDY_STATUS_TYPE+","
					+ "b." + BuddyTable.KEY_BUDDY_FAVORITE+","
					+ "b." + BuddyTable.KEY_BUDDY_DISPLAYNAME+","
					+ "b." + BuddyTable.KEY_BUDDY_DISPLAYNICK+","
					+ "b." + BuddyTable.KEY_BUDDY_INFO+","
					+ "b." + BuddyTable.KEY_BUDDY_CHOSUNG+","
					+ "b." + BuddyTable.KEY_BUDDY_IMG_VERSION
		+ " FROM "  + Tables.TABLE_NAME_CONTACT + " a"
						   + " JOIN " + Tables.TABLE_NAME_BUDDY + " b"
		+ " ON "	+ "((a." + ContactTable.KEY_BUDDY_UUID_MSB + " = " + "b." + BuddyTable.KEY_BUDDY_UUID_MSB
					+ ") AND "
					+ "(a." + ContactTable.KEY_BUDDY_UUID_LSB + " = " + "b." + BuddyTable.KEY_BUDDY_UUID_LSB
					+ "))"
		+ ")";
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w("MTalk", "Upgrading database from version " + oldVersion + " to "
                + newVersion + ", which will destroy all old data");
        db.execSQL("DROP TABLE IF EXISTS notes");
        onCreate(db);
	}
	
	public void clearTable(Context context)
	{
		ContentResolver cr = context.getContentResolver();
		cr.delete(BuddyTable.CONTENT_URI, null, null);
		cr.delete(MessageTable.CONTENT_URI, null, null);
		cr.delete(ContactTable.CONTENT_URI, null, null);
		cr.delete(ViewTable.CONTENT_URI, null, null);
		cr.delete(SuggestionTable.CONTENT_URI, null, null);
		cr.delete(GroupTable.CONTENT_URI, null, null);
	}
}